DISCONNECT ALL;

CREATE DATABASE bookdb;

CONNECT TO bookdb;

CREATE TABLE author (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
  name VARCHAR(20) NOT NULL UNIQUE,
  info XML NOT NULL
);

INSERT INTO author VALUES (DEFAULT,'John Craft','<Info><Email>jc@jc.com</Email><Country>England</Country><YearOfBirth>1948</YearOfBirth></Info>'),
(DEFAULT,'Arnie Bastoft','<Info><Email>bastoft@frei.at</Email><Country>Austria</Country><YearOfBirth>1971</YearOfBirth></Info>'),
(DEFAULT,'Meg Gilmand','<Info><Email>megil@archeo.org</Email><Country>Australia</Country><YearOfBirth>1968</YearOfBirth></Info>'),
(DEFAULT,'Chris Ryan','<Info><Email>chris@egypt.eg</Email><Country>France</Country><YearOfBirth>1944</YearOfBirth></Info>'),
(DEFAULT,'Alan Griff','<Info><Email>ag@mit.edu</Email><Country>USA</Country><YearOfBirth>1972</YearOfBirth></Info>'),
(DEFAULT,'Marty Faust','<Info><Email>marty@nyu.edu</Email><Country>USA</Country><YearOfBirth>1970</YearOfBirth></Info>'),
(DEFAULT,'Celine Biceau','<Info><Email>celine.biceau@tok.cn</Email><Country>Canada</Country><YearOfBirth>1969</YearOfBirth></Info>'),
(DEFAULT,'Carl Sagan','<Info><Email>carlsagan@nasa.gov</Email><Country>USA</Country><YearOfBirth>1913</YearOfBirth></Info>'),
(DEFAULT,'Leslie Brenner','<Info><Email>leslie@yahoo.com</Email><Country>USA</Country><YearOfBirth>1945</YearOfBirth></Info>'),
(DEFAULT,'Jakob Hanson','<Info><Email>jh22@hotmail.com</Email><Country>Sweden</Country><YearOfBirth>1946</YearOfBirth></Info>'),
(DEFAULT,'Mimi Pappas','<Info><Email>mimi@music.com</Email><Country>USA</Country><YearOfBirth>1972</YearOfBirth></Info>'),
(DEFAULT,'Marie Franksson','<Info><Email>franksson@bok.se</Email><Country>Sweden</Country><YearOfBirth>1937</YearOfBirth></Info>'),
(DEFAULT,'Pierre Zargone','<Info><Email>zargone@fans.be</Email><Country>Belgium</Country><YearOfBirth>1968</YearOfBirth></Info>'),
(DEFAULT,'Carl George','<Info><Email>cg@eu.int</Email><Country>France</Country><YearOfBirth>1939</YearOfBirth></Info>'),
(DEFAULT,'Peter Feldon','<Info><Email>pf@eu.int</Email><Country>England</Country><YearOfBirth>1917</YearOfBirth></Info>'),
(DEFAULT,'Lilian Carrera','<Info><Email>lc@eu.int</Email><Country>Spain</Country><YearOfBirth>1922</YearOfBirth></Info>'),
(DEFAULT,'Auna Gonzales Perre','<Info><Email>agp@eu.int</Email><Country>Portugal</Country><YearOfBirth>1942</YearOfBirth></Info>'),
(DEFAULT,'Kostas Andrianos','<Info><Email>ka@eu.int</Email><Country>Greece</Country><YearOfBirth>1936</YearOfBirth></Info>'),
(DEFAULT,'Andreas Shultz','<Info><Email>as@eu.int</Email><Country>Austria</Country><YearOfBirth>1919</YearOfBirth></Info>'),
(DEFAULT,'Antje Liedderman','<Info><Email>al@eu.int</Email><Country>Germany</Country><YearOfBirth>1950</YearOfBirth></Info>'),
(DEFAULT,'Christina Ohlsen','<Info><Email>co@eu.int</Email><Country>Norway</Country><YearOfBirth>1933</YearOfBirth></Info>'),
(DEFAULT,'Sam Davis','<Info><Email>sd@music.com</Email><Country>Mexiko</Country><YearOfBirth>1939</YearOfBirth></Info>'),
(DEFAULT,'Alicia Bing','<Info><Email>bing@bing.be</Email><Country>Belgium</Country><YearOfBirth>1952</YearOfBirth></Info>'),
(DEFAULT,'Linda Evans','<Info><Email>levans@ocean.uot.edu</Email><Country>USA</Country><YearOfBirth>1966</YearOfBirth></Info>'),
(DEFAULT,'Chuck Morrisson','<Info><Email>morrisson@home.to</Email><Country>England</Country><YearOfBirth>1963</YearOfBirth></Info>'),
(DEFAULT,'Kay Morrisson','<Info><Email>morrisson@home.to</Email><Country>England</Country><YearOfBirth>1969</YearOfBirth></Info>'),
(DEFAULT,'James Patterson','<Info><Email>jp@hotmail.com</Email><Country>USA</Country><YearOfBirth>1955</YearOfBirth></Info>'),
(DEFAULT,'Peter de Jonge','<Info><Email>pj@mail.com</Email><Country>USA</Country><YearOfBirth>1961</YearOfBirth></Info>'),
(DEFAULT,'Franc Desteille','<Info><Email>franc@chateau.fr</Email><Country>France</Country><YearOfBirth>1925</YearOfBirth></Info>');


CREATE TABLE book (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
  title VARCHAR(50) NOT NULL UNIQUE,
  originallanguage VARCHAR(20) NOT NULL,
  genre VARCHAR(20)
);

INSERT INTO book VALUES (DEFAULT,'Misty Nights','English','Thriller'),
(DEFAULT,'Archeology in Egypt','English','Educational'),
(DEFAULT,'Database Systems in Practice','English','Educational'),
(DEFAULT,'Contact','English','Science Fiction'),
(DEFAULT,'The Fourth Star','English','Science Fiction'),
(DEFAULT,'Våren vid sjön','Swedish','Novel'),
(DEFAULT,'Dödliga Data','Swedish','Thriller'),
(DEFAULT,'Music Now and Before','English','Educational'),
(DEFAULT,'Midsommar i Lund','Swedish','Novel'),
(DEFAULT,'Encore une fois','French',NULL),
(DEFAULT,'European History','English','Educational'),
(DEFAULT,'Musical Instruments','English','Educational'),
(DEFAULT,'Oceans on Earth','English','Educational'),
(DEFAULT,'The Beach House','English','Novel'),
(DEFAULT,'Le chateau de mon pere','French',NULL);


CREATE TABLE edition (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
  year INTEGER NOT NULL,
  price INTEGER NOT NULL,
  translations XML NOT NULL,
  book INTEGER NOT NULL,
  UNIQUE (book, year),
  FOREIGN KEY (book) REFERENCES book (id)
);

INSERT INTO edition VALUES 
(DEFAULT, 1987, 120, '<Translations><Translation Language="German" Publisher="Kingsly" Price="130"/><Translation Language="French" Publisher="Addison" Price="135"/><Translation Language="Russian" Publisher="Addison" Price="125"/></Translations>', (SELECT id FROM book WHERE title = 'Misty Nights')),
(DEFAULT, 1992, 250, '<Translations><Translation Language="Swedish" Price="340"/><Translation Language="French" Price="320"/></Translations>', (SELECT id FROM book WHERE title = 'Archeology in Egypt')),
(DEFAULT, 1994, 280, '<Translations><Translation Language="Swedish" Publisher="KLC" Price="390"/><Translation Language="French" Publisher="KLC" Price="330"/><Translation Language="Chinese" Publisher="Shou-Ling" Price="280"/></Translations>', (SELECT id FROM book WHERE title = 'Archeology in Egypt')),
(DEFAULT, 1999, 280, '<Translations><Translation Language="French" Publisher="KLC" Price="320"/><Translation Language="Turkish" Publisher="Turk And Turk" Price="300"/><Translation Language="Spanish" Price="300"/></Translations>', (SELECT id FROM book WHERE title = 'Archeology in Egypt')),
(DEFAULT, 2000, 430, '<Translations></Translations>', (SELECT id FROM book WHERE title = 'Database Systems in Practice')),
(DEFAULT, 2002, 440, '<Translations></Translations>', (SELECT id FROM book WHERE title = 'Database Systems in Practice')),
(DEFAULT, 1988, 140, '<Translations><Translation Language="Swedish" Publisher="SCB" Price="160"/><Translation Language="German" Price="140"/><Translation Language="Russian" Publisher="RP" Price="140"/></Translations>', (SELECT id FROM book WHERE title = 'Contact')),
(DEFAULT, 2001, 210, '<Translations><Translation Language="Swedish" Publisher="Bästa Bok" Price="260"/></Translations>', (SELECT id FROM book WHERE title = 'The Fourth Star')),
(DEFAULT, 1982, 45, '<Translations></Translations>', (SELECT id FROM book WHERE title = 'Våren vid sjön')),
(DEFAULT, 1989, 48, '<Translations></Translations>', (SELECT id FROM book WHERE title = 'Våren vid sjön')),
(DEFAULT, 1993, 120, '<Translations></Translations>', (SELECT id FROM book WHERE title = 'Dödliga Data')),
(DEFAULT, 1997, 300, '<Translations><Translation Language="German" Publisher="ABC International" Price="310"/><Translation Language="French" Price="310"/></Translations>', (SELECT id FROM book WHERE title = 'Music Now and Before')),
(DEFAULT, 2001, 335, '<Translations><Translation Language="German" Publisher="ABC International" Price="350"/></Translations>', (SELECT id FROM book WHERE title = 'Music Now and Before')),
(DEFAULT, 1988, 75, '<Translations><Translation Language="Finnish" Publisher="Suomi Bookkii" Price="95"/></Translations>', (SELECT id FROM book WHERE title = 'Midsommar i Lund')),
(DEFAULT, 1997, 120, '<Translations><Translation Language="English" Price="120"/></Translations>', (SELECT id FROM book WHERE title = 'Encore une fois')),
(DEFAULT, 2001, 150, '<Translations><Translation Language="English" Publisher="Pels And Jafs" Price="180"/></Translations>', (SELECT id FROM book WHERE title = 'Encore une fois')),
(DEFAULT, 1998, 650, '<Translations><Translation Language="Greek" Publisher="EU Publishing" Price="650"/><Translation Language="Spanish" Publisher="EU Publishing" Price="650"/><Translation Language="Portuguese" Publisher="EU Publishing" Price="650"/><Translation Language="Italian" Publisher="EU Publishing" Price="650"/><Translation Language="French" Publisher="EU Publishing" Price="650"/><Translation Language="Dutch" Publisher="EU Publishing" Price="650"/><Translation Language="Norwegian" Publisher="EU Publishing" Price="650"/><Translation Language="Swedish" Publisher="EU Publishing" Price="650"/><Translation Language="Finnish" Publisher="EU Publishing" Price="650"/><Translation Language="Danish" Publisher="EU Publishing" Price="650"/><Translation Language="Russian" Publisher="EU Publishing" Price="650"/><Translation Language="Bulgarian" Publisher="EU Publishing" Price="650"/></Translations>', (SELECT id FROM book WHERE title = 'European History')),
(DEFAULT, 1991, 300, '<Translations><Translation Language="Portuguese" Publisher="ABC International" Price="300"/><Translation Language="Chinese" Publisher="ABC International" Price="350"/><Translation Language="Russian" Publisher="ABC International" Price="355"/><Translation Language="Italian" Publisher="ABC International" Price="345"/><Translation Language="Spanish" Publisher="ABC International" Price="335"/></Translations>', (SELECT id FROM book WHERE title = 'Musical Instruments')),
(DEFAULT, 2001, 500, '<Translations><Translation Language="Dutch" Publisher="ABC International" Price="510"/><Translation Language="German" Publisher="ABC International" Price="510"/><Translation Language="Spanish" Publisher="ABC International" Price="505"/></Translations>', (SELECT id FROM book WHERE title = 'Musical Instruments')),
(DEFAULT, 1996, 400, '<Translations><Translation Language="French" Publisher="ABC International" Price="420"/><Translation Language="German" Publisher="ABC International" Price="400"/></Translations>', (SELECT id FROM book WHERE title = 'Oceans on Earth')),
(DEFAULT, 1998, 420, '<Translations><Translation Language="French" Publisher="ABC International" Price="430"/><Translation Language="Spanish" Publisher="ABC International" Price="420"/></Translations>', (SELECT id FROM book WHERE title = 'Oceans on Earth')),
(DEFAULT, 2001, 460, '<Translations><Translation Language="Dutch" Publisher="ABC International" Price="500"/><Translation Language="Danish" Publisher="ABC International" Price="530"/><Translation Language="Italian" Publisher="ABC International" Price="510"/></Translations>', (SELECT id FROM book WHERE title = 'Oceans on Earth')),
(DEFAULT, 2002, 500, '<Translations></Translations>', (SELECT id FROM book WHERE title = 'Oceans on Earth')),
(DEFAULT, 2002, 180, '<Translations></Translations>', (SELECT id FROM book WHERE title = 'The Beach House')),
(DEFAULT, 1964, 65, '<Translations><Translation Language="English" Publisher="Benton Inc" Price="105"/><Translation Language="Spanish" Price="100"/><Translation Language="Greek" Price="100"/><Translation Language="Italian" Publisher="Aurora Publ." Price="95"/><Translation Language="German" Price="100"/></Translations>', (SELECT id FROM book WHERE title = 'Le chateau de mon pere'));


CREATE TABLE authorship (
  book INTEGER NOT NULL,
  author INTEGER NOT NULL,
  PRIMARY KEY (book, author),
  FOREIGN KEY (book) REFERENCES book (id),
  FOREIGN KEY (author) REFERENCES author (id)  
);

INSERT INTO authorship VALUES 
((SELECT id FROM book WHERE title = 'Misty Nights'), (SELECT id FROM author WHERE name = 'John Craft')), 
((SELECT id FROM book WHERE title = 'Archeology in Egypt'), (SELECT id FROM author WHERE name = 'Arnie Bastoft')), 
((SELECT id FROM book WHERE title = 'Archeology in Egypt'), (SELECT id FROM author WHERE name = 'Meg Gilmand')),
((SELECT id FROM book WHERE title = 'Archeology in Egypt'), (SELECT id FROM author WHERE name = 'Chris Ryan')),
((SELECT id FROM book WHERE title = 'Database Systems in Practice'), (SELECT id FROM author WHERE name = 'Alan Griff')),
((SELECT id FROM book WHERE title = 'Database Systems in Practice'), (SELECT id FROM author WHERE name = 'Marty Faust')),
((SELECT id FROM book WHERE title = 'Database Systems in Practice'), (SELECT id FROM author WHERE name = 'Celine Biceau')),
((SELECT id FROM book WHERE title = 'Contact'), (SELECT id FROM author WHERE name = 'Carl Sagan')),
((SELECT id FROM book WHERE title = 'The Fourth Star'), (SELECT id FROM author WHERE name = 'Leslie Brenner')),
((SELECT id FROM book WHERE title = 'Våren vid sjön'), (SELECT id FROM author WHERE name = 'Marie Franksson')),
((SELECT id FROM book WHERE title = 'Dödliga Data'), (SELECT id FROM author WHERE name = 'Jakob Hanson')),
((SELECT id FROM book WHERE title = 'Music Now and Before'), (SELECT id FROM author WHERE name = 'Sam Davis')),
((SELECT id FROM book WHERE title = 'Music Now and Before'), (SELECT id FROM author WHERE name = 'Mimi Pappas')),
((SELECT id FROM book WHERE title = 'Midsommar i Lund'), (SELECT id FROM author WHERE name = 'Marie Franksson')),
((SELECT id FROM book WHERE title = 'Encore une fois'), (SELECT id FROM author WHERE name = 'Pierre Zargone')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Carl George')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Peter Feldon')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Lilian Carrera')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Auna Gonzales Perre')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Kostas Andrianos')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Andreas Shultz')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Antje Liedderman')),
((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Christina Ohlsen')),
((SELECT id FROM book WHERE title = 'Musical Instruments'), (SELECT id FROM author WHERE name = 'Sam Davis')),
((SELECT id FROM book WHERE title = 'Musical Instruments'), (SELECT id FROM author WHERE name = 'Alicia Bing')),
((SELECT id FROM book WHERE title = 'Oceans on Earth'), (SELECT id FROM author WHERE name = 'Linda Evans')),
((SELECT id FROM book WHERE title = 'Oceans on Earth'), (SELECT id FROM author WHERE name = 'Chuck Morrisson')),
((SELECT id FROM book WHERE title = 'Oceans on Earth'), (SELECT id FROM author WHERE name = 'Kay Morrisson')),
((SELECT id FROM book WHERE title = 'The Beach House'), (SELECT id FROM author WHERE name = 'James Patterson')),
((SELECT id FROM book WHERE title = 'The Beach House'), (SELECT id FROM author WHERE name = 'Peter de Jonge')),
((SELECT id FROM book WHERE title = 'Le chateau de mon pere'), (SELECT id FROM author WHERE name = 'Franc Desteille'));


CREATE TABLE publisher (
  name VARCHAR(30) NOT NULL PRIMARY KEY,
  street VARCHAR(30) NOT NULL,
  city VARCHAR(20) NOT NULL,
  postalcode VARCHAR(10) NOT NULL,
  country VARCHAR(20) NOT NULL
);

INSERT INTO publisher VALUES 
('ABC International','7th Bear St.','Berlin','44500','Germany'), 
('Addison','2nd Monet St.','Toulouse','98700','France'), 
('Aurora Publ.','3rd Uffizi Rd.','Florence','49112','Italy'), 
('Benton Inc','4th Queen Ave.','London','34565','England'), 
('Bästa Bok','9th Bokgatan','Stockholm','12309','Sweden'), 
('EU Publishing','45th Beer St.','Brussels','34990','Belgium'), 
('KLC','1st Upplandsv.','Uppsala','34067','Sweden'), 
('Kingsly','98th Waltz St.','Vienna','12369','Austria'), 
('Pels And Jafs','32nd Lake Ave.','Edinburg','87300','Scotland'), 
('RP','34th Red Square','Saint Petersburg','34005','Russia'), 
('SCB','3rd Odengatan','Stockholm','10405','Sweden'), 
('Shou-Ling','12th Great Wall St.','Shanghai','12333','China'), 
('Suomi Bookkii','4th Yksi Rd.','Helsinki','12003','Finland'), 
('Turk And Turk','45th Otoman Ave.','Ankara','23000','Turkey');